package com.sfy.les.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sfy.les.dictionary.DataBaseDictionary;
import com.sfy.les.dictionary.DictionaryInfo;
import com.sfy.les.table.TableFiledInfo;
import com.sfy.les.table.TableIndexInfo;
import com.sfy.les.table.TableInfo;
import com.sfy.les.utils.Common;

public class QueryByJDBC {

	private static final Logger logger = LoggerFactory.getLogger(QueryByJDBC.class);
	
	/**
	 * JDBC查询数据库信息
	 * @param exportParam
	 * @return
	 */
	public static List<TableInfo> queryTableInfo(DataBaseDictionary exportParam, DictionaryInfo dictionaryInfo) throws Exception {
		List<TableInfo> tableInfoList = new ArrayList<TableInfo>();
		Connection conn = null;
		Statement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getConnection(exportParam);
			if(conn == null){
				logger.info("数据库连接失败，强检查数据库连接信息！");
				throw new Exception("数据库连接失败，强检查数据库连接信息！");
			}
			ps = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			//获取查询所有表数据的SQL
			String queryAllTablesInfoSql = dictionaryInfo.getQueryAllTablesInfoSql(exportParam);
			//查询所有表信息
			logger.info("开始获取数据库表信息...");
			rs = ps.executeQuery(queryAllTablesInfoSql);
			while (rs.next()) {
				TableInfo item = new TableInfo();
				String[] fileds = Common.getAttrFromClazz(TableInfo.class);
				for (int j = 0; j < fileds.length; j++) {
					String fieldName = fileds[j];
					if(fieldName == null || "".equals(fieldName)){
					}else{
						try {
							String FIELD_NAME = Common.humpToUnderline(fieldName);
							if(rs.getString(FIELD_NAME) != null){
								Field field = item.getClass().getDeclaredField(fieldName);
								field.setAccessible(true);
								field.set(item, rs.getString(FIELD_NAME));
							}
						} catch (Exception e) {
						}
					}
				}
				tableInfoList.add(item);
			}
		}catch (SQLException e) {
			e.printStackTrace();
			throw new Exception(e);
		} finally{			
			DBConnection.closeConnection(conn, ps, rs);
		}
		//遍历所有表信息，多线程查询表结构
		if(tableInfoList != null && tableInfoList.size() > 0){
			tableInfoList = queryTableStructureByThread(exportParam, dictionaryInfo, tableInfoList);
		}
		
		return tableInfoList;
	}
	
	/**
	 * 多线程查询表结构信息
	 * @param tableInfo
	 * @return
	 * @throws Exception 
	 */
	private static List<TableInfo> queryTableStructureByThread(final DataBaseDictionary exportParam, final DictionaryInfo dictionaryInfo, List<TableInfo> tableInfoList) throws Exception{
		logger.info("共查到"+tableInfoList.size()+"张表，开启多线程和获取表结构信息...");
		final List<TableInfo> tableInfoListNew = new Vector<TableInfo>();
		//创建线程池
		//线程池大小
		Integer nThreads = exportParam.getnThreads();
		if(nThreads == null || nThreads == 0|| nThreads < 0){
			nThreads = 10;
		}
		ExecutorService executorService = Executors.newFixedThreadPool(nThreads);
		final int threadCount = tableInfoList.size();
		// 初始化CountDownLatch，用于线程计数
        final CountDownLatch latch = new CountDownLatch(threadCount);
        //是否异常
        final List<String> exceptionStrList = new Vector<String>();
        for(int i = 0; i < tableInfoList.size(); i++){
        	final TableInfo tableInfo = tableInfoList.get(i);
        	executorService.execute(new Runnable(){
				public void run() {
					Connection conn = null;
					Statement ps = null;
					ResultSet rs = null;
					ResultSet rs2 = null;
					conn = DBConnection.getConnection(exportParam);
					if(conn == null){
						logger.info("数据库连接失败，强检查数据库连接信息！");
					}
					try {
						ps = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
						//查询表结构的SQL
						String queryTableFieldsSql = dictionaryInfo.getQueryTableFieldsInfoSql(exportParam, tableInfo);
						rs = ps.executeQuery(queryTableFieldsSql);
						List<TableFiledInfo> tableFiledInfoList = new ArrayList<TableFiledInfo>();
						while (rs.next()) {
							TableFiledInfo item = new TableFiledInfo();
							String[] fileds = Common.getAttrFromClazz(TableFiledInfo.class);
							for (int j = 0; j < fileds.length; j++) {
								String fieldName = fileds[j];
								if(fieldName == null || "".equals(fieldName)){
								}else{
									try {
										String FIELD_NAME = Common.humpToUnderline(fieldName);
										if(rs.getString(FIELD_NAME) != null){
											Field field = item.getClass().getDeclaredField(fieldName);
											field.setAccessible(true);
											field.set(item, rs.getString(FIELD_NAME));
										}
									} catch (Exception e) {
										//正常的异常捕捉,不需要做处理
									}
								}
							}
							tableFiledInfoList.add(item);
						}
						//赋值表字段信息
						tableInfo.setTableFiledInfoList(tableFiledInfoList);
						//查询表索引的SQL
						String queryTableIndexSql = dictionaryInfo.getQueryTableIndexInfoSql(exportParam, tableInfo);
						rs2 = ps.executeQuery(queryTableIndexSql);
						List<TableIndexInfo> tableIndexInfoList = new ArrayList<TableIndexInfo>();
						while (rs2.next()) {
							TableIndexInfo item = new TableIndexInfo();
							String[] fileds = Common.getAttrFromClazz(TableIndexInfo.class);
							for (int j = 0; j < fileds.length; j++) {
								String fieldName = fileds[j];
								if(fieldName == null || "".equals(fieldName)){
								}else{
									try {
										String FIELD_NAME = Common.humpToUnderline(fieldName);
										if(rs2.getString(FIELD_NAME) != null){
											Field field = item.getClass().getDeclaredField(fieldName);
											field.setAccessible(true);
											field.set(item, rs2.getString(FIELD_NAME));
										}
									} catch (Exception e) {
										//正常的异常捕捉,不需要做处理
									}
								}
							}
							tableIndexInfoList.add(item);
						}
						//赋值表索引信息
						tableInfo.setTableIndexInfoList(tableIndexInfoList);
						
					} catch (SQLException e1) {
						String exceptionStr = "获取数据库连接失败或执行SQL异常：" + e1.getMessage();
						exceptionStrList.add(exceptionStr);
						e1.printStackTrace();
					}  finally{		
						if(rs2 != null){
							try {
								rs2.close();
							} catch (SQLException e) {
								logger.info("关闭rs异常！");
								e.printStackTrace();
							}
						}
						DBConnection.closeConnection(conn, ps, rs);
					}
					//获取建表SQL
					try {
						String tableDdl = dictionaryInfo.getTableDdl(exportParam, tableInfo);
						tableInfo.setTableInitSql(tableDdl);
					} catch (Exception e) {
						logger.info("获取建表语句失败！");
						e.printStackTrace();
					}
					tableInfoListNew.add(tableInfo);
		        	logger.info("表"+tableInfo.getTableName()+"的表结构信息获取完成。。。(" + (threadCount - latch.getCount() + 1) + "/" + threadCount + ")");
		        	latch.countDown();
				}
			});
        }
        latch.await();
    	executorService.shutdown();
    	//如果多线程志雄异常，则抛出异常
    	if(exceptionStrList.size() > 0){
    		throw new Exception(exceptionStrList.get(0));
    	}
		return tableInfoList;
	}
}
